﻿using Anke.SHManage.IDAL;
using Anke.SHManage.Model;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;

namespace Anke.SHManage.MSSQLDAL
{

    public partial class I_InvoiceApprovalDALExt : BaseDAL<I_InvoiceApply>, II_InvoiceApplyDAL
    {

        IDALContext dalContext = new DALContextFactory().GetDALContext();

        AKV7ManageEntities db = DBContextFactory.GetDBContext() as AKV7ManageEntities;


        public void getEditApply(string ApplyID, out I_InvoiceApply info)
        {
            info = (from a in db.I_InvoiceApply
                    where (a.ApplyID.Equals(ApplyID))
                    select a).FirstOrDefault().ToPOCO();
        }

        public object GetInvoiceApplyList(int page, int rows, string sortName, string sortOrder, ref int rowCounts, string centerID, DateTime beginTime, DateTime endTime, string stationID, string personName)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                        select distinct identity(int,1,1) as 行号,
                             ApplyID,
				        CenterCode,
				        StationCode,
				        ApplyUserName,
				        ApplyUserID,
				        ApplyTime,
				        ApplyCount,
				        ApprovalUserName,
				        ApprovalUserID,
				        ApprovalTime,
				        ApprovalState,
				        Remark1,
                         Remark2,
				        tc.名称 as CenterName,
                        ts.名称 as StationName,
				        ApprovalCount,
                        StartNumber,
                        EndNumber

                    into #temp
                    from  I_InvoiceApply   ia
                ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on ia.CenterCode=tc.编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on ia.StationCode=ts.编码 ");

            sb.Append(" where 1=1 ");
            WhereClauseUtility.AddDateTimeGreaterThan("ia.ApplyTime", beginTime, sb);
            WhereClauseUtility.AddDateTimeLessThan("ia.ApplyTime", endTime, sb);
            WhereClauseUtility.AddStringEqual("ia.CenterCode", centerID, sb);
            WhereClauseUtility.AddStringEqual("ia.StationCode", stationID, sb);
            WhereClauseUtility.AddStringLike("ia.ApplyUserName", personName, sb); //2018-12-11 zch

            {
                sb.Append(@"  
                    order by  " + sortName + "  " + sortOrder + " ");
            }

            sb.Append(@" select top " + rows + " A.*  from #temp A where  行号>" + (page - 1) * rows + "  order by 行号 ");
            sb.Append(@"  SELECT count(*) FROM #temp t   drop table #temp ");

            List<I_InvoiceApply> list = new List<I_InvoiceApply>();
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    I_InvoiceApply info = new I_InvoiceApply();

                    info.ApplyID = dr["ApplyID"].ToString();
                    info.backup1 = dr["CenterName"].ToString();
                    info.backup1 = dr["StationName"].ToString();
                    info.ApplyUserName = dr["ApplyUserName"].ToString();
                    info.ApplyUserID = dr["ApplyUserID"].ToString();
                    info.ApplyCount = Convert.ToInt32(dr["ApplyCount"]);
                    info.ApprovalUserName = dr["ApprovalUserName"].ToString();
                    info.ApprovalUserID = dr["ApprovalUserID"].ToString();
                    info.ApprovalUserID = dr["ApprovalUserID"].ToString();
                    info.ApplyTime = Utility.DBConvert.ConvertDBTypeToNullable(dr["ApplyTime"]);
                    info.ApprovalTime = Utility.DBConvert.ConvertDBTypeToNullable(dr["ApprovalTime"]);
                    info.ApprovalCount = Convert.ToInt16(dr["ApprovalCount"]);
                    info.Remark1 = dr["Remark1"].ToString();
                    info.Remark2 = dr["Remark2"].ToString();
                    info.StartNumber = dr["StartNumber"].ToString();
                    info.EndNumber = dr["EndNumber"].ToString();
                    info.ApprovalState = dr["ApprovalState"].ToString();

                    list.Add(info);
                }
            }
            rowCounts = Convert.ToInt32(ds.Tables[1].Rows[0][0]);

            return list;

        }

        /// <summary>
        /// 根据传入的开始号码和结束号码，如果有在已存在的开始号码和结束号码之间就返回True。
        /// </summary>
        /// <param name="txtStartNumber"></param>
        /// <param name="txtEndNumber"></param>
        /// <returns></returns>
        public bool IFExistInvoice(string txtStartNumber, string txtEndNumber)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@" 
            select  StartNumber,EndNumber   FROM  I_InvoiceApply where StartNumber is not null
            ");

            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            DataTable table = ds.Tables[0];
            bool results = false;
            int counts = table.Rows.Count;
            if (counts > 0)
            {
                for (int i = 0; i < counts; i++)
                {
                    string StartNumber = table.Rows[i]["StartNumber"].ToString();
                    string EndNumber = table.Rows[i]["EndNumber"].ToString();
                    //判断只有当开始号码和结束号码 的长度都大于0 
                    if (StartNumber.Length > 0 && EndNumber.Length > 0)
                    {
                        int StartNumberInt = Convert.ToInt32(StartNumber);


                        int EndNumberInt = Convert.ToInt32(EndNumber);

                        int txtStartNumberInt = Convert.ToInt32(txtStartNumber);
                        int txtEndNumberInt = Convert.ToInt32(txtEndNumber);

                        if (StartNumberInt <= txtStartNumberInt && txtStartNumberInt <= EndNumberInt)
                        {
                            results = true;
                        }
                        if (StartNumberInt <= txtEndNumberInt && txtEndNumberInt <= EndNumberInt)
                        {
                            results = true;
                        }
                    }


                }
            }

            return results;
        }


        /// <summary>
        /// 循环添加到字表，在sql里循环效率相比于BLL.add 大大提高
        /// </summary>
        /// <param name="applyCount">申领数量</param>
        /// <param name="startNumber">发票号开始号码</param>
        /// <param name="ApplyID">发票申领的主表ID</param>
        public int AddI_InvoiceApprovalList(Int64 applyCount, Int64 startNumber, string ApplyID)
        {
            //使用sql 循环添加
            StringBuilder sb = new StringBuilder();
            sb.Append(" declare @i int  set @i=0");
            sb.Append(" while @i< ").Append(applyCount); // 2018-12-11 zch 去掉 =
            sb.Append(" begin ");
            sb.Append(" insert into  I_InvoiceApplyList ");
            sb.Append(" values(").Append(ApplyID).Append("+@i,").Append(ApplyID).Append(",").Append(startNumber).Append("+@i,null,null)");
            sb.Append(" set @i = @i +1 ");
            sb.Append(" end ");
            return SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);

        }


        //删除 2016-5-26 新增
        public int deleteI_InvoiceApproval(string ApplyID)
        {
            using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required))
            {
                //使用sql 循环添加
                StringBuilder sb = new StringBuilder();
                sb.Append(" delete from I_InvoiceApplyList");
                sb.Append("  where ApplyID= '").Append(ApplyID).Append("'");

                SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);

                StringBuilder sb1 = new StringBuilder();

                sb1.Append(" update  I_InvoiceApply");
                sb1.Append(" set  ApprovalUserName=NULL,ApprovalUserID=NULL,ApprovalTime=NULL,ApprovalState='申领中',ApprovalCount=0,StartNumber=NULL,EndNumber=NULL,Remark2=NULL");
                sb1.Append("  where ApplyID= '").Append(ApplyID).Append("'");

                int re = SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sb1.ToString(), null);
                ts.Complete();
                return re;

            }
        }

        public object GetINClearList(int page, int rows, string sortName, string sortOrder, ref int rowCounts, string ClearNumber, DateTime beginTime, DateTime endTime)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                        select distinct identity(int,1,1) as 行号,
                           ID
                          ,ClearDate
                          ,ClearPerson
                          ,ClearNumber
                          ,LinkTaskCode
                          ,LinkPatientOrder
                    into #temp
                    from  I_InvoiceApplyClear   ia
                ");

            sb.Append(" where 1=1 ");
            WhereClauseUtility.AddDateTimeGreaterThan("ia.ClearDate", beginTime, sb);
            WhereClauseUtility.AddDateTimeLessThan("ia.ClearDate", endTime, sb);
            WhereClauseUtility.AddStringLike("ia.ClearNumber", ClearNumber, sb);
            {
                sb.Append(@"  
                    order by  " + sortName + "  " + sortOrder + " ");
            }

            sb.Append(@" select top " + rows + " A.*  from #temp A where  行号>" + (page - 1) * rows + "  order by 行号 ");
            sb.Append(@"  SELECT count(*) FROM #temp t   drop table #temp ");

            List<I_InvoiceApplyClear> list = new List<I_InvoiceApplyClear>();
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    I_InvoiceApplyClear info = new I_InvoiceApplyClear();

                    info.ID = new Guid(Convert.ToString(dr["ID"]));
                    info.ClearDate = Utility.DBConvert.ConvertDBTypeToNullable(dr["ClearDate"]);
                    info.ClearPerson = dr["ClearPerson"].ToString();
                    info.ClearNumber = dr["ClearNumber"].ToString();
                    info.LinkTaskCode = dr["LinkTaskCode"].ToString();
                    list.Add(info);
                }
            }
            rowCounts = Convert.ToInt32(ds.Tables[1].Rows[0][0]);

            return list;

        }



        //2018-12-11 zch  统计报表
        public DataTable GetPersonInvoiceCondition()
        {
            //2019-06-13 plq add 若表I_InvoiceApplyList中InvoiceNumber字段的值在表M_PatientCharge中出现，则发票已使用(isUsed=1)
            StringBuilder str = new StringBuilder();
            str.Append(@"                 
                     update I_InvoiceApplyList
                     set isUsed=1
                     from I_InvoiceApplyList ia,M_PatientCharge pc
                     where ia.InvoiceNumber=pc.InvoiceNumber ");
            SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, str.ToString(), null);

            StringBuilder sb = new StringBuilder();
            sb.Append(@"                 
                     SELECT   
                    领用人=a.ApplyUserName
                    ,领用=  count(ia.ApplyDetailID)
                    ,未用= sum(case when  ia.isUsed  is  null   then  1 else 0 end)
                    ,已用=sum(case when  ia.isUsed = 1 then  1 else 0 end)
                    FROM I_InvoiceApply a
                    left join I_InvoiceApplyList ia on a.ApplyID = ia.ApplyID
					where  a.ApprovalState = '已审批'
                    group by ApplyUserName  ");
       
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            return ds.Tables[0];
        }

        //2018-12-11 zch
        public DataTable GetPersonInvoiceUsed(DateTime lastTime, string personName, bool isUsed)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"                 
                     SELECT   
                    领用人=a.ApplyUserName
					,领用日期 = a.ApprovalTime
                    ,使用日期 = ia.backup2
                    ,已用=ia.InvoiceNumber
                    FROM I_InvoiceApply a
                    left join I_InvoiceApplyList ia on a.ApplyID = ia.ApplyID ");
            if (isUsed)
                sb.Append(" where  ia.isUsed = 1   ");
            else
                sb.Append(" where  ia.isUsed is null ");
            //            WhereClauseUtility.AddStringLike("dr.StationName", stationCode, sb);
            WhereClauseUtility.AddStringLike("a.ApplyUserName", personName, sb);
            WhereClauseUtility.AddDateTimeLessThan("a.ApprovalTime", lastTime, sb);
            //            WhereClauseUtility.AddStringLike("dr.Stretcher1Name", stretcher, sb);
            //            SqlParameter[] prams = new SqlParameter[] {
            //                new SqlParameter("@StartTime", start),   
            //                new SqlParameter("@EndTime", end)               
            //            };
            sb.Append("	order by InvoiceNumber    ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            return ds.Tables[0];

        }

        //2018-12-11 zch
        public int UpdateInvoiceState(string invoiceNumber)
        {
           string backup2 = DateTime.Now.ToString("yyyy-MM-dd");
            StringBuilder sb = new StringBuilder();
            sb.Append(" update I_InvoiceApplyList ");
            sb.Append(" set isUsed =1,backup2='" + backup2 + "'"); 
            sb.Append(" where InvoiceNumber = '" + invoiceNumber + "' ");
            return SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
        }

    }
}
